import os
import Core.Config as Config
import Core.WindFunctions as Wind
import Core.Gadget as Gadget
import Core.IO as IO
import Core.Logger
import pandas as pd
import DataProcess
from xlrd import xldate_as_tuple
import datetime
from DataProcess.General import Automatic_Download_DailyQuote


#
def TryToRequestMutualFundReport(symbol, reportDate):
    # ---
    contents = []
    contents.append("fund_stm_issuingdate_qty")  # 季度报告披露日期

    # ---
    params = {}
    rptDateStr = Gadget.ToDateString(reportDate)
    params["rptDate"] = rptDateStr

    # ---
    # print("Requesting @" + str(datetime.now()))
    values = Wind.WSS(symbol, contents, params)[0]

    minDate = datetime.datetime(1900,1,1)
    if values["fund_stm_issuingdate_qty"] < minDate:
        return False
    return True


# ---获取 Mutual Fund 报告信息 WSS+WEST 共同使用---
def Download_MutualFund_Report(database, symbol, reportDate):
    #
    print("Download_MutualFund_Report", symbol, reportDate.date())
    logger = Core.Logger.GetLogger("MutualFund")
    #
    collectionName = "Report"

    # ---Field----
    contents = []
    contents.append("fund_stm_issuingdate_qty")  # 季度报告披露日期
    contents.append("prt_totalasset")  # 基金资产总值
    contents.append("prt_netasset")  # 基金资产净值
    contents.append("unit_total")  # 基金份额
    #
    contents.append("prt_stockvalue")  # 股票投资市值
    contents.append("prt_hkstockvalue")  # 港股投资市值
    contents.append("prt_bondvalue")  # 债券投资市值
    contents.append("prt_fundvalue")  # 基金投资市值
    contents.append("prt_cash")  # 银行存款
    contents.append("prt_mmivalue")  # 货币市场工具市值
    contents.append("prt_other")  # 其他资产
    #
    contents.append("prt_governmentbond")  # 国债市值
    contents.append("prt_financialbond")  # 金融债市值
    contents.append("prt_centralbankbill")  # 央行票据市值
    contents.append("prt_convertiblebond")  # 可转债市值
    contents.append("prt_corporatebonds")  # 企业债市值
    contents.append("prt_mtnvalue")  # 中期票据市值
    contents.append("prt_cpvalue")  # 短期融资券市值
    contents.append("prt_CDs")  # 同业存单市值
    contents.append("prt_otherbond")  # 其他债券
    # 20200729 增加
    contents.append("prt_fundnetasset_total")  # 资产净值（合计）
    contents.append("prt_stocktonav")  # 股票市值占基金资产净值比
    contents.append("prt_stocktoasset")  # 股票市值占基金资产总值比
    contents.append("prt_bondtonav")  # 债券市值占基金资产总值比
    contents.append("prt_bondtoasset")  # 债券市值占基金资产总值比
    #
    contents.append("holder_institution_holdingpct")  # 机构持有占比

    # ---Necessary Parameters---
    params = {}
    reportDate = reportDate.replace(tzinfo=None)
    datestr = Gadget.ToDateString(reportDate)
    params["rptDate"] = datestr

    # ---Request Functions---
    values = Wind.WSS(symbol, contents, params)[0]

    #---Check data validation---
    dt1 = values["fund_stm_issuingdate_qty"]

    if values["prt_totalasset"] == None and values["prt_netasset"] == None:
        print("WSSData_MuturalFund_Report_prt_totalasset=None_MaynotExist : " + symbol + " " + str(reportDate));
        logger.info(symbol + " Fund No Reports " + str(reportDate))
        return

    if (values["fund_stm_issuingdate_qty"] < reportDate):#fund.ReleaseDate < datetime
        print(symbol + " FixReleaseDate")

    # ---Rename---
    dataObject = {}
    dataObject["Symbol"] = symbol
    # dataObject["ReportDate"] = Gadget.ToDateTimeString(reportDate)
    # dataObject["StdReportDate"] = Gadget.ToUTCDateTime(reportDate)
    # dataObject["ReleaseDate"] = Gadget.ToDateTimeString(values["fund_stm_issuingdate_qty"])
    releaseDate = values["fund_stm_issuingdate_qty"]
    dataObject["Report_Date"] = reportDate.date()
    dataObject["Release_Date"] = releaseDate.date()
    dataObject["Date"] = releaseDate.date()
    dataObject["Key2"] = dataObject["Symbol"] + "_" + Gadget.ToDateString(dataObject["Report_Date"])
    #
    dataObject["Total_Asset"] = values["prt_totalasset"]
    dataObject["Net_Asset"] = values["prt_netasset"]
    dataObject["Fund_Shares"] = values["unit_total"]
    #
    dataObject["Net_Asset_Total"] = values["prt_fundnetasset_total"]
    del values["prt_fundnetasset_total"]
    #
    dataObject["institute_hold_proportion"] = values["holder_institution_holdingpct"]
    del values["holder_institution_holdingpct"]

    # ---Wind Data---
    for k, v in values.items():
        # s = "Wind." + k
        # dataObject[s] = values[k]
        dataObject[k] = values[k]

    # ---Upsert to Database---
    target = {}
    target["Report_Date"] = dataObject["Report_Date"]
    target["Symbol"] = symbol
    database.Upsert("MutualFund", collectionName, target, dataObject)
    #
    return dataObject


# ---基金报表中的持仓明细---
def Download_MutualFund_Positions(database, symbol, reportDate, releaseDate=None):
    #
    print("Download_MutualFund_Positions", symbol, reportDate.date())
    logger = Core.Logger.GetLogger("MutualFund")
    #
    strReportDate = Gadget.ToDateString(reportDate)
    params = {}
    params["rptdate"] = Gadget.ToDateString(reportDate)
    params["windcode"] = symbol

    #---注意：单位万股---
    params["field"] = "stock_code,hold_number,marketvalueofstockholdings"
    data = Wind.WSET("allstockhelddetaill", params)

    #
    if data == None:
        print(symbol + " NoPositions " + params["rptdate"])
        #IO.AppendToFile("D:/Data/MutualFundLog/NoReportsPosition.txt", symbol + " NoPositions " + str(reportDate))
        logger.info(symbol + " NoPositions " + str(reportDate))
        return

    positions = []
    # --- Field=date,wind_code,sec_name,i_weight ---
    for d in data:
        entry = {}
        entry["Symbol"] = d["stock_code"]
        #
        if d["hold_number"] != None:
            entry["Amount"] = d["hold_number"] * 10000
            entry["Qty"] = entry["Amount"]
        else:
            entry["Amount"] = None
            entry["Qty"] = None
            logger.info(symbol + " NoHoldername " + d["stock_code"] + " " + str(reportDate))

        #
        if d["marketvalueofstockholdings"] != None:
            entry["Equity"] = d["marketvalueofstockholdings"] * 10000
        else:
            entry["Equity"] = None
        #
        entry["MutualFund"] = symbol
        entry["Report_Date"] = reportDate.date()
        entry["Release_Date"] = releaseDate

        if isinstance(releaseDate, datetime.datetime):
            entry["Date"] = releaseDate.date()
        else:
            entry["Date"] = releaseDate
        entry["DateTime"] = releaseDate
        entry["Key2"] = symbol + "_" + strReportDate + "_" + entry["Symbol"]
        positions.append(entry)

    # dataObject = {}
    # dataObject["Positions"] = positions

    # ---Upsert---
    target = {}
    target["Report_Date"] = Gadget.ToDateTimeString(reportDate)
    target["Symbol"] = symbol
    # database.Upsert("MutualFund", "Reports", target, dataObject)
    database.Upsert_Many("MutualFund", "Positions", [], positions)
    kkwood = 0


def Check_MutualFund_MissingReport(database):
    #
    datetimeNow = Gadget.ToUTCDateTime(datetime.datetime.now())
    filter = {}
    # filter["$or"] = [{"InvestType2": "偏股混合型基金"}, {"InvestType2": "灵活配置型基金"}, {"InvestType2": "普通股票型基金"}]
    # filter["DateTime1"] = {"$gte": datetime1, "$lte": datetime2}
    instruments = database.findWithFilter("Instruments", "MutualFund", filter)
    for instrument in instruments:
        symbol = instrument["Symbol"]
        # if symbol != "184801.OF":
        #    continue

        datetime1 = instrument["DateTime1"]
        datetime2 = instrument["DateTime2"]

        if Gadget.DeleteTimeZone(datetime1) < datetime.datetime(1900, 1, 1):
            continue

        if Gadget.DeleteTimeZone(datetime2) > datetime.datetime(2100, 1, 1):
            datetime2 = datetimeNow

        if datetime2 > datetimeNow:
            datetime2 = datetimeNow

        datetime1 = instrument["DateTime1"] + datetime.timedelta(days=120)
        filter = {}
        filter["Symbol"] = symbol
        filter["StdReportDate"] = {"$gte": datetime1, "$lte": datetime2}
        reports = database.findWithFilter("MutualFund", "Reports", filter)

        if len(reports) == 0 and len(datetimes) == 0:
            # its okay
            continue

        reportByReportDate = {}
        if len(reports) != 0:
            for report in reports:
                existed = reportByReportDate.get(report["ReportDate"])
                if existed != None:
                    print(symbol + " DuplicateReport " + reportDate)
                reportByReportDate[report["ReportDate"]] = report

        datetimes = Gadget.GenerateReportDates(datetime1, datetime2)
        for dt in datetimes:
            reportDate = Gadget.ToDateTimeString(dt)
            report = reportByReportDate.get(reportDate)
            if report == None:
                print(symbol + " MissingReport " + reportDate)


def Check_MutualFund_MissingPositions(database):
    datetimeNow = Gadget.ToUTCDateTime(datetime.datetime.now())
    filter = {}
    filter["$or"] = [{"InvestType2": "偏股混合型基金"}, {"InvestType2": "灵活配置型基金"}, {"InvestType2": "普通股票型基金"}]
    instruments = database.findWithFilter("Instruments", "MutualFund", filter)

    totalMissing = 0
    for instrument in instruments:
        symbol = instrument["Symbol"]
        filter = {}
        #filter["DateTime1"] = {"$gte": datetime1, "$lte": datetime2}
        filter["Symbol"] = symbol
        filter["Positions"] = {"$exists": False}
        reports = database.findWithFilter("MutualFund", "Reports", filter)
        if len(reports)!= 0:
            totalMissing += len(reports)
            for report in reports:
                print(symbol + " MissingPositions " + report["ReportDate"])

    print("TotalMissingPositions " + str(totalMissing))


# 依据已经存在的基金Report，补齐可能缺失的基金Position
def Fix_MutualFund_Positions(database, datetime1, datetime2, start_index=0):
    # Reports 的存在日期
    query = []
    query.append(("report_date", ">=", datetime1))
    query.append(("report_date", "<=", datetime2))
    documents_reports = database.Find("MutualFund", "Report", filter=query, projection=["symbol", "report_date", "release_date"])
    dfReports = Gadget.DocumentsToDataFrame(documents_reports)
    #
    reportDatesBySymbol = {}
    dfGroups = dfReports.groupby("symbol")
    for dfGroup in dfGroups:
        symbol = dfGroup[0]
        reportDatesBySymbol[symbol] = list(dfGroup[1])

    # 过滤非股票型基金
    filter = []
    filter.append(["Invest_Type1", "=", "股票型基金"])
    filter.append(["or", "Invest_Type1", "=", "混合型基金"])
    # filter.append(["Invest_Type1", "=", "债券型基金"])
    # filter.append(["Invest_Type1", "=", "货币市场型基金"])

    instruments = database.Find("Instruments", "MutualFund", filter, projection=["symbol"])
    dfInstruments = Gadget.DocumentsToDataFrame(instruments)
    # print(dfInstruments)
    dfReports = pd.merge(dfInstruments, dfReports, how="inner", on="symbol")

    # Positions 的存在日期
    documents_position = database.Find("MutualFund", "Positions", filter=query, projection=["mutualfund", "report_date", "release_date"])
    dfPositions = Gadget.DocumentsToDataFrame(documents_position)
    positionsReportDateBySymbol = {}
    dfGroups = dfPositions.groupby("mutualfund") # 按照基金代码聚合一次
    for dfGroup in dfGroups:
        symbol = dfGroup[0]
        dfGroupByDates = dfGroup[1].groupby("report_date") # 按照报告日聚合一次
        positionsReportDateBySymbol[symbol] = []
        for dfGroup2 in dfGroupByDates:
            dt = dfGroup2[0]
            positionsReportDateBySymbol[symbol].append(dt)
    #
    for index, row in dfReports.iterrows():
        #
        if index < start_index:
            continue
        symbol = row["symbol"]
        print("Process", index, symbol)
        reportDate = row["report_date"]
        releaseDate = row["release_date"]
        #
        positionsReportDates = []
        if symbol in positionsReportDateBySymbol:
            positionsReportDates = positionsReportDateBySymbol[symbol]
        if reportDate not in positionsReportDates:
            print("Missing Positions", symbol, reportDate)
            pass
            reportDate = datetime.datetime.combine(reportDate, datetime.time(0))
            DataProcess.Download_MutualFund_Data.Download_MutualFund_Positions(database, symbol, reportDate, releaseDate)
            b = 0


def Fix_MissingReports_from_Excel():
    header, data = IO.ReadExcelFile("D:/Data/MutualFundLog/MissingReport1.xlsx")

    instruments = []
    for d in data:
        symbol = d[header["Symbol"]]
        a = d[header["DateTime"]]
        reportDate = datetime.datetime(*xldate_as_tuple(a, 0))
        mf = database.findWithFilter("Instruments", "MutualFund", {"Symbol": symbol})
        instruments = [mf[0]]
        datetimes = [reportDate]
        Download_MutualFund_Reports(database, instruments, datetimes)


def Fix_MissingReports_at_ReportDate(database, report_date):
    #
    df_mutualfund = database.Get_Instruments_DataFrame(instrument_type="mutualfund")
    # 未退市的
    df_mutualfund = df_mutualfund[df_mutualfund["datetime2"] > report_date]
    symbols = df_mutualfund["symbol"].tolist()
    #
    df_reports = database.GetDataFrame("financial_data", "mutualfund_report",
                                       filter=[("report_date", report_date.date())], projection=["symbol"], sort=[])
    symbols_exist = df_reports["symbol"].tolist()

    for symbol in symbols:
        if symbol in symbols_exist:
            continue
        #
        document = Download_MutualFund_Report(database, symbol, report_date)
        if document:
            release_date = document["Release_Date"]
            Download_MutualFund_Positions(database, symbol, report_date, release_date)
        a = 0


# ---统计指定日期的报告数量（掌握目前更新进度）---
def Check_MutualFund_DownloadProgress(database, datetime1):

    #
    datetime1 = Gadget.ToUTCDateTime(datetime1)

    # 退市不早于 datetime1
    notDelisted = {"DateTime2": {"$gte": datetime1}}
    datetime2 = datetime1

    # 上市早于 datetime2, 如果有datetime2
    alreadayListed = {"DateTime1": {"$lte": datetime2}}

    #
    filter = {}

    if datetime2:
        filter["$and"] = [notDelisted, alreadayListed]
    else:
        filter = notDelisted

    #
    instruments = database.find("Instruments", "MutualFund", query=filter)

    #
    statements = database.find("MutualFund", "Reports", query={"StdReportDate": datetime1})

    instrumentCount = len(instruments)
    statementCount = len(statements)

    #
    print("Collected Reports", statementCount, format(statementCount/instrumentCount, '.0%'), " @ ", datetime1)


# ----Mutual FundReports---
# ---Total Reports!!!---
def Batch_Download_MutualFund_Reports(database, instruments, datetime1, datetime2=None, forceToUpdate=False, startIndex=0):
    #
    if datetime2 == None:
        datetime2 = datetime1
    datetime2 = Gadget.DeleteTimeZone(datetime2)
    #
    count = 0
    start = False
    for mf in instruments:
        symbol = mf["symbol"]
        count += 1
        #
        if count < startIndex:
           continue
        #
        if symbol == "000116.OF":
            a = 0
        #
        print("Download MutualFund Reports", symbol, " count ", str(count))
        #
        if datetime1 < datetime.datetime(1900, 1, 1):
            continue
        # 未上市
        if mf["datetime1"] > datetime2:
            continue
        # 已下市
        if mf["datetime2"] < datetime1:
            continue
        #
        realDateTime1 = datetime1
        if mf["datetime1"] > datetime1:
            realDateTime1 = mf["datetime1"]

        realDateTime2 = datetime2
        if mf["datetime2"] < datetime2:
            realDateTime2 = mf["datetime2"]

        reports = []
        reportDatesInDataBase = []
        if forceToUpdate == False:
            query = []
            query.append(("symbol", symbol))
            query.append(("report_date", ">=", realDateTime1))
            query.append(("report_date", "<=", realDateTime2))
            reports = database.Find("MutualFund", "Report", filter=query)
            #
            for report in reports:
                reportDatesInDataBase.append(report["report_date"])

        #
        # realDateTime1 = Gadget.ToLocalDateTime(realDateTime1)
        # realDateTime2 = Gadget.ToLocalDateTime(realDateTime2)
        reportDatesTotal = Gadget.GenerateReportDates(realDateTime1, realDateTime2, asDate=True)

        reportDates = list(set(reportDatesTotal).difference(set(reportDatesInDataBase)))
        #
        for reportDate in reportDates:
            # Convert Date tp datetime
            reportDate = datetime.datetime(reportDate.year, reportDate.month, reportDate.day)
            #
            print(symbol, "TryToFetch @", reportDate)
            if TryToRequestMutualFundReport(symbol, reportDate):
                # Request Report
                dataObject = Download_MutualFund_Report(database, symbol, reportDate)
                # Request Positions
                if dataObject != None:
                    Download_MutualFund_Positions(database, symbol, reportDate, dataObject["Release_Date"])
            else:
                print(symbol, "ReportNotReady @", reportDate)
            #
            a = 0
    #
    Fix_MutualFund_Positions(database, datetime1, datetime2)


def Automatic_Download_MutualFund_Reports(database, datetime2, startIndex=0):
    datetime1 = datetime.datetime(2015, 1, 1)
    #
    filter = []
    # filter.append(["Invest_Type1", "=", "股票型基金"])
    # filter.append(["or", "Invest_Type1", "=", "混合型基金"])
    # filter.append(["or", "Invest_Type1", "=", "债券型基金"])
    # filter.append(["Invest_Type1", "=", "货币市场型基金"])
    instruments = database.Find("financial_data", "Instrument_MutualFund", filter)

    Batch_Download_MutualFund_Reports(database,
                                      instruments,
                                      datetime1,
                                      datetime2=datetime2,
                                      forceToUpdate=False,
                                      startIndex=startIndex)


def Download_MutualFund_Extend_Fields_Sub(symbols, report_date, contents_dict):

    # ---Necessary Parameters---
    params = {}
    reportDate = report_date.replace(tzinfo=None)
    datestr = Gadget.ToDateString(reportDate)
    params["rptDate"] = datestr

    contents = []
    for key, value in contents_dict.items():
        contents.append(key)

    # ---Request Functions---
    values = Wind.WSS(symbols, contents, params)

    documents = []
    for i in range(len(symbols)):
        symbol = symbols[i]
        d = values[i]
        #
        dataObject = {}
        dataObject["Key2"] = symbol + "_" + Gadget.ToDateString(reportDate.date())
        for wind_name, map_name in contents_dict.items():
            value = d[wind_name]
            if str(value) == "nan":
                value = None
            dataObject[map_name] = value

        #
        documents.append(dataObject)

    return documents


# ---扩展字段20200914---
def Download_MutualFund_Extend_Fields(database, report_date, contents):
    #
    print("Download_MutualFund_Report_Extends", report_date.date())
    #
    df_mutualfund = database.Get_Instruments_DataFrame(instrument_type="mutualfund")
    symbols = df_mutualfund["symbol"].tolist()

    # 分批提取
    step = 100
    start = 0
    for i in range(0, len(symbols), step):
        print("Download Mutual Fund Extend from", i, "to", i + step)
        if i < start:
            continue
        partial_symbols = symbols[i:i + step]
        documents = Download_MutualFund_Extend_Fields_Sub(partial_symbols, report_date, contents)
        database.Upsert_Many("MutualFund", "Report", {}, documents)
        a = 0
    a = 0


def Extend_Fields_20200914():
    contents = {}
    contents["holder_institution_holdingpct"] = "institute_hold_proportion"
    #
    report_date = datetime.datetime(2020, 9, 30)
    Download_MutualFund_Extend_Fields(database, report_date, contents)


if __name__ == '__main__':

    # ---Connecting Database---
    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    # 启动Wind API
    Wind.w.start()

    # ---更新 公募基金季度报告（含持仓信息）---
    reportDate = datetime.datetime(2020, 9, 30)
    # Automatic_Download_MutualFund_Reports(database, reportDate, startIndex=0)  # 001825.OF
    # Fix_MutualFund_Positions(database, reportDate, reportDate, start_index=0) # 27137

    # ---基金报告冷启动（第一次建立数据库）---
    datetime1 = datetime.datetime(1991, 1, 1)
    datetime2 = datetime.datetime(2020, 11, 1)

    filter = []
    filter.append(("symbol", "005505.OF"))
    instruments = database.Find("Instruments", "MutualFund",filter)
    # Batch_Download_MutualFund_Reports(database, instruments, datetime1, datetime2, True)

